
ALTER    function fn_backlog_report(

@Product_ID 		varchar(15),
@Sales_Order_Number 	varchar(15),
@Customer_ID 		varchar(15),
@Sales_Person 		varchar(15),
@asOfDate 		varchar(15),
@Backlog_Type 		varchar(10),
@defaultPrompt 		varchar(20),
@Category		varchar(100))

returns @fn_results Table (Customer_Name varchar(65),Customer_Number varchar(50), Sales_Order_Number varchar(50),Customer_PO varchar(50), 
SO_Date datetime, Sales_Person varchar(50), Sales_Rep varchar(50), Sales_Hold varchar(10), Marketing_Hold varchar(10),Manufacturing_Hold varchar(10), 
Finance_Hold varchar(10), Req_Date datetime, Schd_Date datetime, Rev_Date datetime, Part_Number varchar(50), Req_Qyt int, Rem_Qty int ,
Unit_Price float, Status varchar(20), LineItem_No int,RevLevel int, BacklogType varchar(20), BookingMonth int, BookingYear int, BookingQuarter int, QuarterEnd int,ActYear int,FinYear int)


as
begin

declare @date_in datetime, @date_out datetime
declare @results Table(Customer_Name varchar(65),Customer_Number varchar(50), Sales_Order_Number varchar(50),Customer_PO varchar(50), 
SO_Date datetime, Sales_Person varchar(50), Sales_Rep varchar(50), Sales_Hold varchar(10), Marketing_Hold varchar(10),Manufacturing_Hold varchar(10), 
Finance_Hold varchar(10), Req_Date datetime, Schd_Date datetime, Rev_Date datetime, Part_Number varchar(50), Req_Qyt int, Rem_Qty int ,
Unit_Price float, Status varchar(20), LineItem_No int,RevLevel int, BacklogType varchar(20), BookingMonth int, BookingYear int, BookingQuarter int, QuarterEnd int,ActYear int,FinYear int)

declare @booking_date  datetime -- Added by Basha (11/21/2003)
declare @so_BK_Mt_I int, @so_Bk_Yr_I int, @so_Bk_Qt_I int, @so_Bk_Qt_End_I int -- Added by Basha (11/21/2003)
declare @act_year int , @fin_year int -- Added By Basha (11/23/2003)


if @Backlog_Type = 'all'
begin
	set @backlog_Type = '0'
end

declare @lineItemCursor CURSOR

if (@Product_ID= '0' and @Sales_Order_Number = '0' and @Customer_ID ='0' and @Sales_Person = '0')
begin
     set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.Regional_Rep, 
		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
		cool.cool_unit_cost, cool.COOL_Status 
	from 	customer_open_Order_lineItem cool, product, sales_order, customer 
	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = 
		customer.customer_id and cool.COOL_Status<> 'SHIPPED' and Sales_Order.SO_Is_Sample = 1
end

else if (@Product_ID<> '0' and @Sales_Order_Number = '0' and @Customer_ID ='0' and @Sales_Person = '0')
begin
	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.Regional_Rep, 
		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
		cool.cool_unit_cost,cool.COOL_Status 
	from 	customer_open_Order_lineItem cool, product, sales_order, customer 
	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and cool.product_id = 
		@product_id and cool.COOL_Status<> 'SHIPPED' and Sales_Order.SO_Is_Sample = 1
end

else if (@Product_ID= '0' and @Sales_Order_Number <> '0' and @Customer_ID ='0' and @Sales_Person = '0')
begin
	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.Regional_Rep, 
		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
		cool.cool_unit_cost,cool.COOL_Status 
	from	customer_open_Order_lineItem cool, product, sales_order, customer 
	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and 
		sales_order.salesOrder_no = @sales_order_number and cool.COOL_Status <> 'SHIPPED' and Sales_Order.SO_Is_Sample = 1
end

else if (@Product_ID= '0' and @Sales_Order_Number = '0' and @Customer_ID <>'0' and @Sales_Person = '0')
begin
	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.Regional_Rep, 
		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
		cool.cool_unit_cost,cool.COOL_Status 
	from	customer_open_Order_lineItem cool, product, sales_order, customer 
	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and 
		sales_order.customer_id = @customer_id and cool.COOL_Status <> 'SHIPPED' and Sales_Order.SO_Is_Sample = 1
end

else if (@Product_ID= '0' and @Sales_Order_Number = '0' and @Customer_ID ='0' and @Sales_Person <> '0')
begin
	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.Regional_Rep, 
		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
		cool.cool_unit_cost,cool.COOL_Status 
	from customer_open_Order_lineItem cool, product, sales_order, customer 
	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and 
		sales_order.so_sales_person = @sales_person and cool.COOL_Status <> 'SHIPPED' and Sales_Order.SO_Is_Sample = 1
end

else if (@Product_ID<> '0' and @Sales_Order_Number <> '0' and @Customer_ID ='0' and @Sales_Person = '0')
begin
	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.Regional_Rep, 
		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
		cool.cool_unit_cost,cool.COOL_Status 
	from customer_open_Order_lineItem cool, product, sales_order, customer 
	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and cool.product_id = 
		@product_id and sales_order.salesOrder_no = @sales_order_number and cool.COOL_Status <> 'SHIPPED' and Sales_Order.SO_Is_Sample = 1
end

else if (@Product_ID<> '0' and @Sales_Order_Number = '0' and @Customer_ID <>'0' and @Sales_Person = '0')
begin
	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.Regional_Rep, 
		cool.cool_requested_date, cool.cool_schedule_date,cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
		cool.cool_unit_cost,cool.COOL_Status 
	from customer_open_Order_lineItem cool, product, sales_order, customer 
	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and cool.product_id = 
		@product_id and sales_order.customer_id = @customer_id and cool.COOL_Status<> 'SHIPPED' and Sales_Order.SO_Is_Sample = 1
end

else if (@Product_ID<> '0' and @Sales_Order_Number = '0' and @Customer_ID ='0' and @Sales_Person <> '0')
begin
	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.Regional_Rep, 
		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
		cool.cool_unit_cost,cool.COOL_Status 
	from customer_open_Order_lineItem cool, product, sales_order, customer 
	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and cool.product_id = 
		@product_id and sales_order.so_sales_person = @sales_person and cool.COOL_Status<> 'SHIPPED' and Sales_Order.SO_Is_Sample = 1
end

else if (@Product_ID= '0' and @Sales_Order_Number <> '0' and @Customer_ID <>'0' and @Sales_Person = '0')
begin
	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.Regional_Rep, 
		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
		cool.cool_unit_cost,cool.COOL_Status 
	from customer_open_Order_lineItem cool, product, sales_order, customer 
	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and 
		sales_order.salesorder_no = @sales_order_number and sales_order.customer_id = @customer_id and cool.COOL_Status <> 'SHIPPED' and Sales_Order.SO_Is_Sample = 1
end

else if (@Product_ID= '0' and @Sales_Order_Number <> '0' and @Customer_ID ='0' and @Sales_Person <> '0')
begin
	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.Regional_Rep, 
		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
		cool.cool_unit_cost,cool.COOL_Status 
	from customer_open_Order_lineItem cool, product, sales_order, customer 
	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and 
		sales_order.salesorder_no = @sales_order_number and sales_order.so_sales_person = @sales_person and cool.COOL_Status <> 'SHIPPED' and Sales_Order.SO_Is_Sample = 1
end

else  if (@Product_ID= '0' and @Sales_Order_Number <> '0' and @Customer_ID <>'0' and @Sales_Person <> '0')
begin
	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.Regional_Rep, 
		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
		cool.cool_unit_cost,cool.COOL_Status 
	from customer_open_Order_lineItem cool, product, sales_order, customer 
	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and 
		sales_order.salesorder_no = @sales_order_number and sales_order.so_sales_person = @sales_person and sales_order.customer_id = @customer_id and cool.COOL_Status <> 'SHIPPED' and Sales_Order.SO_Is_Sample = 1
end

else if (@Product_ID= '0' and @Sales_Order_Number = '0' and @Customer_ID <>'0' and @Sales_Person <> '0')
begin
	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.Regional_Rep, 
		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
		cool.cool_unit_cost,cool.COOL_Status from customer_open_Order_lineItem cool, product, sales_order, customer where cool.salesorder_ID = 
		sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and 
		sales_order.customer_id = @customer_id and sales_order.so_sales_person = @sales_person and cool.COOL_Status<> 'SHIPPED' and Sales_Order.SO_Is_Sample = 1
end

else if (@Product_ID<> '0' and @Sales_Order_Number <> '0' and @Customer_ID <>'0' and @Sales_Person <> '0')
begin
	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.Regional_Rep, 
		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
		cool.cool_unit_cost,cool.COOL_Status 
	from customer_open_Order_lineItem cool, product, sales_order, customer 
	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and 
		sales_order.salesorder_no = @sales_order_number and sales_order.so_sales_person = @sales_person and 
		sales_order.customer_id = @customer_id and cool.product_id = @product_id and cool.COOL_Status <> 'SHIPPED' and Sales_Order.SO_Is_Sample = 1
end

else if (@Product_ID<> '0' and @Sales_Order_Number = '0' and @Customer_ID <>'0' and @Sales_Person <> '0')
begin
	set @lineItemCursor = CURSOR for select cool.lineitem_id, Customer.c_name, customer.c_number, sales_order.salesorder_no, 
		sales_order.so_customer_PO_number, sales_order.SO_Booking_Date, sales_order.so_sales_person, sales_order.Regional_Rep, 
		cool.cool_requested_date, cool.cool_schedule_date, cool.cool_Revised_Date, product.part_number, cool.cool_qty, 
		cool.cool_unit_cost,cool.COOL_Status 
	from customer_open_Order_lineItem cool, product, sales_order, customer 
	where cool.salesorder_ID = sales_order.salesorder_id and cool.product_id = product.product_id and sales_order.customer_id = customer.customer_id and 
		sales_order.salesorder_no = @sales_order_number and sales_order.so_sales_person = @sales_person and sales_order.customer_id = @customer_id and cool.product_id = 
		@product_id and cool.COOL_Status<> 'SHIPPED' and Sales_Order.SO_Is_Sample = 1
end

declare @customer_Name varchar (65), @customer_number varchar (50), @so_no varchar (50)
declare @line_item_id int, @customer_po varchar (50), @so_date datetime, @s_person varchar(50)
declare @sales_rep varchar(50), @req_date datetime, @part_number varchar(50), @quantity int
declare @unit_cost float, @sch_date datetime, @rev_date datetime,@status varchar (20), @line_item_no int
declare @COOL_Is_Cancel int, @sales_hold varchar(50), @mktg_hold varchar(50), @mfg_hold varchar(50)
declare @fin_hold varchar(50), @rev_level int, @rev_so_id int


declare @quarter_cal_date datetime -- Added by Basha (11/23/2004

open @lineItemCursor 
fetch next from @lineItemCursor into @line_item_id,@customer_Name,@customer_number,@so_no,@customer_po,@so_date, 
		@s_person, @sales_rep,  @req_date,@sch_date,@rev_date,@part_number, @quantity, @unit_cost, @status

	while @@fetch_status = 0
	begin
		
		-- Begin : Added by Basha (11/21/2003)
		
		if (@Category = 'reqDockDate')
		begin
			set @quarter_cal_date = @req_date
		end
		else if (@Category = 'ScheduleDate')
		begin
			set @quarter_cal_date = @sch_date
		end
		else if (@Category = 'revDate')
		begin
			if ( @rev_date is not null)	
			begin
				set @quarter_cal_date = @rev_date
			end
			else
			begin
				set @quarter_cal_date = @sch_date
				set @rev_date = @sch_date
			end
		end
		else if (@Category = 'salesOrder')
		begin
			set @quarter_cal_date = @so_date
		end


		if( @quarter_cal_date is not null)
		begin
			set @so_Bk_Yr_I = DATEPART(yyyy, @quarter_cal_date)
			set @act_year = @so_Bk_Yr_I
			set @so_BK_Mt_I = DATEPART(m, @quarter_cal_date)
			-- Modified by Prabhanjan on 27 Jul 2004. Calculation of Quarters is modified to use Jan to Dec.
			--if (@so_BK_Mt_I >=2 and @so_BK_Mt_I <=4)
			if (@so_BK_Mt_I >=1 and @so_BK_Mt_I <=3)
			begin
				set @so_Bk_Qt_I = 1
				--set @so_Bk_Qt_End_I =4
				set @so_Bk_Qt_End_I =3

				set @fin_year = @so_Bk_Yr_I
			end
			--else if (@so_BK_Mt_I >=5 and @so_BK_Mt_I <=7)
			else if (@so_BK_Mt_I >=4 and @so_BK_Mt_I <=6)
			begin
				set @so_Bk_Qt_I = 2
				--set @so_Bk_Qt_End_I =7
				set @so_Bk_Qt_End_I =6

				set @fin_year = @so_Bk_Yr_I
			end
			--else if (@so_BK_Mt_I >=8 and @so_BK_Mt_I <=10)
			else if (@so_BK_Mt_I >=7 and @so_BK_Mt_I <=9)
			begin
				set @so_Bk_Qt_I = 3
				--set @so_Bk_Qt_End_I =10
				set @so_Bk_Qt_End_I =9

				set @fin_year = @so_Bk_Yr_I
			end						
			--else if ((@so_BK_Mt_I =1) or (@so_BK_Mt_I >=11 and @so_BK_Mt_I <=12))
			else if (@so_BK_Mt_I >=10 and @so_BK_Mt_I <=12)
			begin
				set @so_Bk_Qt_I = 4
				set @so_Bk_Qt_End_I =12

				set @fin_year = @so_Bk_Yr_I

				/*
				if (@so_BK_Mt_I = 1) -- Added by Basha (11/23/2003)
					set @fin_year = @so_Bk_Yr_I - 1

				if @so_Bk_Mt_I = 12 or @so_Bk_Mt_I = 11 
				begin
					set @fin_year = @so_Bk_Yr_I
					set @so_Bk_Yr_I = @so_Bk_Yr_I+1
				end
				*/	
			end
		end
		else
		begin
			set @act_year = null
			set @fin_year = null

			set @so_BK_Mt_I = 0
			set @so_Bk_Yr_I = null
			set @so_Bk_Qt_I = 0
			set @so_Bk_Qt_End_I = 0
		end
		-- End : Added by Basha (11/21/2003)		






		select @rev_so_id = salesorder_id from sales_order where salesorder_no = @so_no and SO_Is_Sample = 1
		select @rev_level = max(ChangeNo) from change_sales_order where salesorder_id = @rev_so_id and CSO_Is_Sample = 1

		if @rev_level is null
			set @rev_level = 0

		if @status = 'APPROVED'
		begin
			declare @temp_status varchar (20) -- , @temp_ps varchar (20)

			
			-- Condition Modified By Basha (11/13/2003)
			
			declare temp_status_cursor cursor for 
			select distinct CST.CST_Status --,  CST.Label 
			from 	customer_shipping_table CST, 
				customer_open_order_lineitem COOL,
				customer_shipping_line_item_table cslit
			where COOL.LineItem_ID = @line_item_id and cool.LineItem_ID = cslit. LineItem_ID and cslit.customershipping_id = cst.customershipping_id and cslit.cslit_status = 'SHIPPED' -- cslit.cslit_status <> 'CANCELLED'

			

			set @temp_status = null -- Added by Basha (11/13/2003)

			open temp_status_cursor
			fetch next from temp_status_cursor into @temp_status --, @temp_ps


			-- Begin : @temp_status is null condition	
			if @temp_status is null
			begin
				select @line_item_no = COOL_Line_Item_No, @temp_status=cool_status 
				from customer_open_order_lineitem 
				where LineItem_ID = @line_item_id 

				--the status had to be set to cancelled if the COOL_Is_Cancel is set to 0

				select @COOL_Is_Cancel=COOL_Is_Cancel 
				from customer_open_order_lineitem 
				where salesorder_id = (select salesorder_id from sales_order where salesorder_no = @so_no and SO_Is_Sample = 1) and cool_line_item_no=@line_item_no

				--select COOL_Is_Cancel from customer_open_order_lineitem where salesorder_id = (select salesorder_id from sales_order where salesorder_no = 10382) and cool_line_item_no=1

				if @COOL_Is_Cancel = 0
				begin
					set @temp_status= 'CANCELLED'
					
				end  

				insert into @results (Customer_Name,Customer_Number, Sales_Order_Number,Customer_PO, SO_Date, Sales_Person, Sales_Rep, 
					Sales_Hold, Marketing_Hold,Manufacturing_Hold, Finance_Hold, Req_Date, Schd_Date, Rev_Date, Part_Number, Req_Qyt, Rem_Qty, Unit_Price, Status, 
					LineItem_No,RevLevel, BacklogType,BookingMonth , BookingYear , BookingQuarter , QuarterEnd , ActYear,FinYear)
				values (@customer_Name,@customer_number,@so_no,@customer_po,@so_date,@s_person,@sales_rep,@sales_hold, 
					@mktg_hold, @mfg_hold, @fin_hold, @req_date,@sch_date, @rev_date, @part_number, @quantity, @quantity, @unit_cost, 
					@temp_status,@line_item_no,@rev_level, 'Open', @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@act_year,@fin_year) 

				set @line_item_id = null
				set @temp_status = null
				set @rev_level = null
				set @rev_so_id = null
				set @COOL_Is_Cancel=null

				--there are some nulls now. We dont want the nulls to be repeated 

				set @rev_date = null
			end
			-- End : @temp_status is null condition
	
			while @@fetch_status = 0
			begin
				if @temp_status is not null 
				begin
					
					select @line_item_no = COOL_Line_Item_No 
					from customer_open_order_lineitem where LineItem_ID = @line_item_id 

					if @temp_status is not null
					begin
                                                /* Commented By Basha (11/12/2003)
						declare statusCursor cursor for
						select distinct CST.CST_Status -- Modified by Basha (11/13/2003)
						from customer_shipping_table CST, customer_open_order_lineitem COOL
						where COOL.SalesOrder_ID=CST.SalesOrder_ID and COOL.LineItem_ID = @line_item_id and CST.CST_Status <> 'CANCELLED' -- and cst.Label = @temp_ps
	
						open statusCursor
						fetch next from statusCursor into @temp_status
	
						--  Begin : @@fetch_status = 0 for statusCursor
						while @@fetch_status = 0
						begin
						*/
							
	
							-- Begin : @temp_status <> 'SHIPPED'
							
							if @temp_status <> 'SHIPPED'
							begin 
								set @rev_level = 0
								
		
								select @COOL_Is_Cancel=COOL_Is_Cancel 
								from customer_open_order_lineitem 
								where salesorder_id = (select salesorder_id from sales_order where salesorder_no = @so_no and SO_Is_Sample = 1) and cool_line_item_no=@line_item_no
	
								if @COOL_Is_Cancel = 0
								begin
									set @temp_status= 'CANCELLED'
									
								end

								declare @ship_qty int
	
								select @ship_qty = isNUll(sum(cslit_qty_shipped),0)
								from customer_shipping_line_item_table 
								where customer_shipping_line_item_table.lineitem_id = @line_item_id  and customer_shipping_line_item_table.cslit_status = 'SHIPPED' 
	
								set @ship_qty  = @quantity - @ship_qty 
									
								insert into @results (Customer_Name,Customer_Number, Sales_Order_Number,Customer_PO, SO_Date, Sales_Person, Sales_Rep, 
									Sales_Hold, Marketing_Hold,Manufacturing_Hold, Finance_Hold, Req_Date, 
									Schd_Date, Rev_Date, Part_Number, Req_Qyt, Rem_Qty, Unit_Price, Status, 
									LineItem_No,RevLevel, BacklogType,BookingMonth , BookingYear , BookingQuarter , QuarterEnd,ActYear,FinYear)
								values (@customer_Name,@customer_number,@so_no,@customer_po,@so_date,@s_person,@sales_rep,@sales_hold, 
									@mktg_hold, @mfg_hold, @fin_hold, @req_date,@sch_date, @rev_date, 
									@part_number,@quantity , @ship_qty, @unit_cost, @temp_status,@line_item_no,@rev_level, 'Open', @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@act_year,@fin_year) 
	
								set @rev_level = null
								set @rev_so_id = null
								set @COOL_Is_Cancel=null
	
							end
							-- End : @temp_status <> 'SHIPPED'
	
							-- Begin : @temp_status = 'SHIPPED'
							else if @temp_status = 'SHIPPED'
							begin
							
								--declare @ship_qty int --Commented By Basha (11/13/2003)
								
								select @ship_qty = isNull(sum(cslit_qty_shipped) , 0)
								from customer_shipping_line_item_table 
								where customer_shipping_line_item_table.lineitem_id = @line_item_id  and customer_shipping_line_item_table.cslit_status = 'SHIPPED' 
	
								set @ship_qty  = @quantity - @ship_qty 

								/* Commented by Basha (11/13/2003)
								if @ship_qty is null
								begin
									set @ship_qty = 0
								end
								*/
				
								
								
								if (@ship_qty  > 0)
								begin
									select @COOL_Is_Cancel=COOL_Is_Cancel 
									from customer_open_order_lineitem 
									where salesorder_id = (select salesorder_id from sales_order where salesorder_no = @so_no and SO_Is_Sample = 1) and cool_line_item_no=@line_item_no
	
									if @COOL_Is_Cancel = 0
									begin
										set @temp_status= 'CANCELLED'
										
									end
	
									insert into @results (Customer_Name,Customer_Number, Sales_Order_Number,Customer_PO, SO_Date, Sales_Person, Sales_Rep, 
										Sales_Hold, Marketing_Hold,Manufacturing_Hold, Finance_Hold, Req_Date, 
										Schd_Date, Rev_Date, Part_Number, Req_Qyt, Rem_Qty, Unit_Price, Status, LineItem_No,RevLevel, BacklogType,BookingMonth , BookingYear , BookingQuarter , QuarterEnd,ActYear,FinYear)
									values (@customer_Name,@customer_number,@so_no,@customer_po,@so_date,@s_person,@sales_rep,@sales_hold, 
										@mktg_hold, @mfg_hold, @fin_hold, @req_date,@sch_date, @rev_date, @part_number, @quantity, @ship_qty, @unit_cost,@status,@line_item_no,@rev_level, 'Open', @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@act_year,@fin_year) 
	
									set @rev_level = null
									set @rev_so_id = null
									set @COOL_Is_Cancel = null
	        
								end
													
							end
							-- End : @temp_status = 'SHIPPED'
							
						/* Commented By Basha (11/13/2003)
							fetch next from statusCursor into @temp_status
						end
						--  End : @@fetch_status = 0 for statusCursor 
					
						close statusCursor
						deallocate statusCursor
						*/
					end
					--  End : @temp_status is not null
				
					else
					begin
						select @COOL_Is_Cancel=COOL_Is_Cancel 
						from customer_open_order_lineitem where salesorder_id = (select salesorder_id from sales_order 
						where salesorder_no = @so_no and SO_Is_Sample = 1) and cool_line_item_no=@line_item_no
	
						if @COOL_Is_Cancel = 0
						begin
							set @temp_status= 'CANCELLED'
							
						end

						insert into @results (Customer_Name,Customer_Number, Sales_Order_Number,Customer_PO, SO_Date, Sales_Person, Sales_Rep, Sales_Hold, Marketing_Hold,Manufacturing_Hold, Finance_Hold, Req_Date, Schd_Date, Rev_Date, Part_Number, Req_Qyt, Rem_Qty, Unit_Price, Status, LineItem_No,RevLevel, BacklogType,BookingMonth , BookingYear , BookingQuarter , QuarterEnd,ActYear,FinYear)
						values (@customer_Name,@customer_number,@so_no,@customer_po,@so_date,@s_person,@sales_rep,@sales_hold, 
							@mktg_hold, @mfg_hold, @fin_hold, @req_date,@sch_date, @rev_date, @part_number, @quantity, @quantity, @unit_cost, @temp_status,@line_item_no,@rev_level,'Open', @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@act_year,@fin_year) 
	
						set @rev_level = null
						set @rev_so_id = null
						set @COOL_Is_Cancel = null
	
					end
				end
				--  End : @temp_status is not null
			
				fetch next from temp_status_cursor into @temp_status --, @temp_ps

				-- Commented By Basha (11/13/2003)
				-- set @temp_status= NULL
				-- set @line_item_id = NULL

			end
			-- End : @@fetch_status = 0 for temp_status_cursor

			close temp_status_cursor
			deallocate temp_status_cursor
                                   
		end 
		-- End : APPROVED
		else
		begin
			
			select @sales_hold = hold_reason_code from sales_hold_table where hold_by_group = 'SALES' and lineitem_id = @line_item_id
			select @mktg_hold = hold_reason_code from sales_hold_table where hold_by_group = 'MARKETING' and lineitem_id = @line_item_id
			select @mfg_hold = hold_reason_code from sales_hold_table where hold_by_group = 'OPERATIONS' and lineitem_id = @line_item_id
			select @fin_hold = hold_reason_code from sales_hold_table where hold_by_group = 'FINANCE' and lineitem_id = @line_item_id

			if @sales_hold = '0' set @sales_hold = 'X' 
			if @mktg_hold = '0'  set @mktg_hold = 'X' 
			if @mfg_hold = '0'   set @mfg_hold = 'X' 
			if @fin_hold = '0'    set @fin_hold = 'X' 

			declare @shipped_qty int, @lineitem_id int
  
			select @lineitem_id = lineitem_id from customer_shipping_line_item_table where lineitem_id = @line_item_id and cslit_status = '_SHIPPED_' 
  
  			if (@lineitem_id is not null)
			begin
				select @shipped_qty = sum(cslit_qty_shipped) from customer_shipping_line_item_table 
				where customer_shipping_line_item_table.lineitem_id = @line_item_id  and customer_shipping_line_item_table.cslit_status = '_SHIPPED_' or customer_shipping_line_item_table.cslit_status = 'SHIPPED' 
      
				set @shipped_qty = @quantity-@shipped_qty
				if (@shipped_qty > 0)
				begin
					select @line_item_no = COOL_Line_Item_No from customer_open_order_lineitem where LineItem_ID = @line_item_id  

					select @COOL_Is_Cancel=COOL_Is_Cancel from customer_open_order_lineitem 
					where salesorder_id = (select salesorder_id from sales_order where salesorder_no = @so_no and SO_Is_Sample = 1) and cool_line_item_no=@line_item_no

					if @COOL_Is_Cancel = 0
					begin
						set @temp_status= 'CANCELLED'
						
					end

					

					insert into @results (Customer_Name,Customer_Number, Sales_Order_Number,Customer_PO, 
						SO_Date, Sales_Person, Sales_Rep, Sales_Hold, Marketing_Hold,Manufacturing_Hold, Finance_Hold, Req_Date, Schd_Date, 
						rev_Date, Part_Number, Req_Qyt, Rem_Qty, Unit_Price, Status, LineItem_No,RevLevel, BacklogType,BookingMonth , BookingYear , BookingQuarter , QuarterEnd,ActYear,FinYear)
					values (@customer_Name,@customer_number,@so_no,@customer_po,@so_date,@s_person,@sales_rep,@sales_hold, 
						@mktg_hold, @mfg_hold, @fin_hold, @req_date,@sch_date, @rev_date, @part_number,@quantity, @shipped_qty, @unit_cost,@status, @line_item_no,@rev_level, 'Hold', @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@act_year,@fin_year) 

					select @rev_level = null, @rev_so_id = null, @COOL_Is_Cancel = null

				end
			end
       			else 
			begin
				select @line_item_no = COOL_Line_Item_No from customer_open_order_lineitem where LineItem_ID = @line_item_id 

				select @COOL_Is_Cancel = COOL_Is_Cancel from customer_open_order_lineitem 
				where salesorder_id = (select salesorder_id from sales_order where salesorder_no = @so_no and SO_Is_Sample = 1) and cool_line_item_no = @line_item_no

				set @ship_qty = 0 -- Added by Basha (11/18/2003)
				if @COOL_Is_Cancel = 0
				begin
					set @status= 'CANCELLED'
					

					-- Added By Basha (11/13/2003)
					select @ship_qty = isNUll(sum(cslit_qty_shipped),0)
					from customer_shipping_line_item_table 
					where customer_shipping_line_item_table.lineitem_id = @line_item_id  and customer_shipping_line_item_table.cslit_status = 'SHIPPED' 
				
				end

				set @ship_qty  = @quantity - @ship_qty -- Moved from if statement
	

				insert into @results (Customer_Name,Customer_Number, Sales_Order_Number,Customer_PO, SO_Date, Sales_Person, Sales_Rep, 
					Sales_Hold, Marketing_Hold,Manufacturing_Hold, Finance_Hold, Req_Date, Schd_Date, Rev_Date, Part_Number, Req_Qyt, Rem_Qty, Unit_Price, Status,LineItem_No,RevLevel, BacklogType,BookingMonth , BookingYear , BookingQuarter , QuarterEnd,ActYear,FinYear)
				values (@customer_Name,@customer_number,@so_no,@customer_po,@so_date,@s_person,@sales_rep,@sales_hold, 
					@mktg_hold, @mfg_hold, @fin_hold, @req_date,@sch_date, @rev_date, @part_number,@quantity, @ship_qty, @unit_cost,@status,@line_item_no,@rev_level, 'Hold', @so_BK_Mt_I , @so_Bk_Yr_I , @so_Bk_Qt_I , @so_Bk_Qt_End_I,@act_year,@fin_year) 



				-- select @rev_level = null, @rev_so_id = null, @COOL_Is_Cancel = null
  
			end
			


			select @customer_name = NULL, @customer_number = NULL,  @so_no = NULL, @line_item_id = NULL
			select @customer_po = NULL, @so_date = NULL, @s_person = NULL, @sales_rep  = NULL, @req_date =NULL
			select @part_number = NULL,@quantity = NULL, @unit_cost = NULL, @sales_hold = NULL, @mktg_hold  = NULL
			select @mfg_hold = NULL, @fin_hold = NULL, @sch_date = NULL, @shipped_qty = NULL, @lineitem_id = NULL
			select @line_item_no = NULL, @COOL_Is_Cancel = null, @rev_date = null
		end

		-- End : Not APPROVED

		set @so_date = null -- Added by Basha (11/21/2003)

		fetch next from @lineItemCursor into  @line_item_id,@customer_Name,@customer_number,@so_no,@customer_po,@so_date, 
			@s_person, @sales_rep,  @req_date,@sch_date,@rev_date, @part_number, @quantity, @unit_cost, @status
	end
	
	-- End : @@fetch_Status = 0 for lineItemCursor

	close @lineItemCursor
	deallocate @lineItemCursor

	
	if @Backlog_Type <> '0' 
	begin
		
		if (@Category = 'reqDockDate')
		begin
			insert @fn_results select * from @results where BacklogType = @Backlog_Type order by Req_Date
			return
		end
		else if (@Category = 'ScheduleDate')
		begin
			insert @fn_results select * from @results where BacklogType = @Backlog_Type order by Schd_Date
			return
		end
		else if (@Category = 'revDate')
		begin
			insert @fn_results select * from @results where BacklogType = @Backlog_Type order by Rev_Date
			return
		end
		else if (@Category = 'salesOrder')
		begin
			insert @fn_results select * from @results where BacklogType = @Backlog_Type order by Sales_Order_Number
			return
		end
		
	end
	else
	begin
		
		--select * from @results 
		--select * from @results order by So_Date , BookingYear , BookingQuarter
		if (@Category = 'reqDockDate')
		begin
			insert @fn_results select * from @results order by Req_Date
			return
		end
		else if (@Category = 'ScheduleDate')
		begin
			insert @fn_results select * from @results order by Schd_Date
			return
		end
		else if (@Category = 'revDate')
		begin
			insert @fn_results select * from @results order by Rev_Date
			return
		end
		else if (@Category = 'salesOrder')
		begin
			insert @fn_results select * from @results order by Sales_Order_Number
			return
		end
	end
	
	return
end

